﻿' ------------------------------------------------------------------------------
' A few simple helper methods for checking, creating, loading and dropping
' tables from the database.
' ------------------------------------------------------------------------------
' This file is part of the SQL Server 2008 Technology Demonstration. This
' application was designed and coded by Eric Weitzman, LandTime Inc, for
' Microsoft Corporation. Contact Ed Katibah at Microsoft about this demo.
' Copyright © 2008 Microsoft Corporation. All rights reserved.
' ------------------------------------------------------------------------------

Public Module DatabaseUtils

    Public Sub CreateTable(ByVal table As String, ByVal sql As String, ByVal connection As Connection)
        Dim cmd As SqlCommand = connection.CreateCommand()
        Try
            cmd.CommandText = sql
            ReportSqlCommand("create table", cmd)
            cmd.ExecuteNonQuery()
            ReportTrace("created table " & table)
        Catch ex As SqlException
            ReportException(ex)
        End Try
    End Sub

    Public Sub DropTable(ByVal table As String, ByVal connection As Connection)
        Dim cmd As SqlCommand = connection.CreateCommand()
        Try
            cmd.CommandText = "DROP TABLE " & table
            ReportSqlCommand("drop table", cmd)
            cmd.ExecuteNonQuery()
            ReportTrace("dropped table " & table)
        Catch ex As SqlException
            If ex.Number <> 3701 Then ReportException(ex)
        End Try
    End Sub

    Public Function OkayToCreate(ByVal table As String, ByVal overwrite As Boolean, ByVal connection As Connection) As Boolean
        If overwrite Then Return True
        If Not TableExists(table, connection) Then Return True
        ReportError("cannot create table " & table & " because it already exists")
        Return False
    End Function

    Public Function TableExists(ByVal table As String, ByVal connection As Connection) As Boolean
        Dim cmd As SqlCommand = connection.CreateCommand()
        Try
            cmd.CommandText = String.Format("SELECT COUNT(*) FROM {0}", table)
            ReportSqlCommand("get count", cmd)
            cmd.ExecuteScalar()
            'ReportTrace("table exists: " & table)
            Return True
        Catch ex As SqlException
            'ReportTrace("table does not exist: " & table)
            Return False
        End Try
    End Function

    Public Function GetDataTable(ByVal table As String, ByVal connection As Connection, Optional ByVal top As Integer = 0) As DataTable
        Dim cmd As SqlCommand = connection.CreateCommand
        cmd.CommandText = "SELECT" & DirectCast(IIf(top = 0, "", " TOP " & top), String) & " * FROM " & table
        Return GetDataTable(table, cmd, connection)
    End Function

    Public Function GetDataTable(ByVal table As String, ByVal cmd As SqlCommand, ByVal connection As Connection) As DataTable
        Try
            Dim adapter As New SqlDataAdapter()
            TweakCommand(table, cmd, connection)
            adapter.SelectCommand = cmd

            ReportTrace("loading query results into DataTable from table " & table)
            ReportSqlCommand("loading query results into DataTable from table " & table, cmd)
            ReportStartTimer()
            Dim ds As New DataSet
            adapter.Fill(ds, table)
            'Dim n As Integer = 0
            'Dim chunk As Integer = 10
            'While adapter.Fill(ds, n, 10, table) = chunk
            '    ReportSqlOperation("read chunk of " & chunk & ", total of " & n, "filled row in adapter from datatable")
            '    n += chunk
            'End While
            ReportShowTimer("DataTable loaded")
            ReportTrace("DataTable loaded")
            Return ds.Tables.Item(table)
        Catch ex As Exception
            ReportException(ex)
            Return Nothing
        End Try
    End Function

    Public Sub TweakCommand(ByVal table As String, ByVal cmd As SqlCommand, ByVal connection As Connection)
        If table <> Schema.Photo Then Return

        Dim sql As String = cmd.CommandText
        Dim separator() As String = New String() {" * "}
        Dim parts() As String = sql.Split(separator, StringSplitOptions.RemoveEmptyEntries)
        If parts.Length <> 2 Then Return

        Dim db As New Database()
        Dim sb As New System.Text.StringBuilder
        For Each column As String In db.ColumnNames(Schema.Photo, connection)
            If column = Schema.ImageFilestreamColumn Then
                sb.Append(column).Append(".PathName() AS ").Append(Schema.ImageFilestreamColumnDotPathName).Append(", ")
            Else
                sb.Append(column).Append(", ")
            End If
        Next
        If sb.Length > 2 Then sb.Remove(sb.Length - 2, 2)

        sql = parts(0) & " " & sb.ToString & " " & parts(1)
        cmd.CommandText = sql
    End Sub
End Module
